﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace AssetManagementSystem.Model.Dao.MSAccess {
    public class SysPropertyDAO : AssetManagementSystem.Model.IDao.ISysPropertyDAO {

        //The MSAccess database connection to use in all data access operations.
        private OleDbConnection dbConn;

        /// <summary>
        /// Constructs a new AssetDAO using a MSAccess database connection string.
        /// </summary>
        /// <param name="connectionString">MSAccess database connection string</param>
        public SysPropertyDAO(string connectionString) {
            //create new database connection
            this.dbConn = new OleDbConnection(connectionString);
            //test the new database connection
            try {
                //make sure the connection can be opened.
                dbConn.Open();
            } catch (Exception e) {
                throw e;
            } finally {
                //close connection if goes went wrong.
                dbConn.Close();
            }
        }

        //====================================================================
        //  CRUD METHODS - CREATE, READ, UPDATE, DELETE
        //====================================================================

        /// <summary>
        /// Create a new system property in the database
        /// </summary>
        /// <param name="property">The property to be stored in the database</param>
        /// <param name="value">The value of the property to be stored</param>
        public void CreateProperty(string property, string value) {
            //Make sure the property doesn't already exist
            if (PropertyExists(property)) {
                throw new Exception("A property with the name provided already exists, could not be saved.");
            } else {
                try {
                    //store the new property
                    dbConn.Open();
                    OleDbCommand oleDbCmd = new OleDbCommand(
                        "INSERT INTO SysProperties ([Property], [Val]) " +
                        "VALUES(@property, @val, @GroupName, @Description)",
                        dbConn);
                    oleDbCmd.Parameters.AddWithValue("@property", property);
                    oleDbCmd.Parameters.AddWithValue("@val", value);
                    //make sure the database changed
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("property not stored. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Retrieve a single property from the database
        /// </summary>
        /// <param name="property">The name of the property to be retrieved</param>
        /// <returns>The property's value</returns>
        public string GetProperty(string property) {
            String value = "";
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM SysProperties WHERE ([Property] = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("Property", property);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //if more or less than 1 property is returned for this name, throw an exception
                if (dataTable.Rows.Count != 1) {
                    throw new Exception(dataTable.Rows.Count.ToString() + " records found for property " + property + " expected 1");
                }
                //create a property object for the record returned
                foreach (DataRow row in dataTable.Rows) {
                    value = row["Val"].ToString();
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //return the value
            return value;
        }

        /// <summary>
        /// Returns a dictionary of all system properties
        /// </summary>
        /// <returns>A dictionary of all system properties</returns>
        public Dictionary<string, string> GetAllProperties() {
            Dictionary<string, string> allProperties = new Dictionary<string, string>();
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM SysProperties", dbConn);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //create an entry for each record returned
                foreach (DataRow row in dataTable.Rows) {
                    allProperties.Add(
                        row["Property"].ToString(),
                        row["Val"].ToString()
                        );
                }
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }

            //return the properties
            return allProperties;
        }

        /// <summary>
        /// Update the details of a system property
        /// </summary>
        /// <param name="property">The property to be stored in the database</param>
        /// <param name="value">The value of the property to be stored</param>
        public void UpdateProperty(string property, string value) {
            //Ensure property already exists
            if (!PropertyExists(property)) {
                throw new Exception("Cannot update property, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("UPDATE SysProperties SET [Val] = ? WHERE [Property] = ?",
                        dbConn);
                    dbConn.Open();
                    oleDbCmd.Parameters.AddWithValue("Val", value);
                    oleDbCmd.Parameters.AddWithValue("Property", property);
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Property not updated. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }

        /// <summary>
        /// Delete data for a single property from the database.
        /// </summary>
        /// <param name="property">The property to be deleted from the database</param>
        public void DeleteProperty(string property) {
            //Ensure asset already exists
            if (!PropertyExists(property)) {
                throw new Exception("Cannot delete property, existing details not found in database.");
            } else {
                try {
                    OleDbCommand oleDbCmd = new OleDbCommand("DELETE * FROM SysProperties WHERE [Property] = ?", dbConn);
                    oleDbCmd.Parameters.AddWithValue("Property", property);
                    dbConn.Open();
                    if (oleDbCmd.ExecuteNonQuery() < 1) {
                        throw new Exception("Property not deleted. An unknown error occured");
                    }
                } catch (Exception e) {
                    throw e;
                } finally {
                    dbConn.Close();
                }
            }
        }


        //====================================================================
        //  UTILITY METHODS - USEFUL WHEN PERFORMING OTHER OPERATIONS
        //====================================================================

        /// <summary>
        /// Returns true if a property exists with the specified name.
        /// </summary>
        /// <param name="property">The name of the property</param>
        /// <returns>True if an property exists with the specified name, otherwise false.</returns>
        public bool PropertyExists(string property) {
            try {
                dbConn.Open();
                OleDbDataAdapter dAdapter = new OleDbDataAdapter("SELECT * FROM SysProperties WHERE ([Property] = ?)", dbConn);
                dAdapter.SelectCommand.Parameters.AddWithValue("Property", property);
                DataTable dataTable = new DataTable();
                dAdapter.Fill(dataTable);
                //return true if at least one property was returned.
                return dataTable.Rows.Count > 0;
            } catch (Exception e) {
                throw e;
            } finally {
                dbConn.Close();
            }
        }

        //====================================================================
        //  END - UTILITY METHODS
        //====================================================================


    }
}
